SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO



CREATE PROCEDURE [dbo].[usp_StatisticsFacultyUpdate]
	(
      @param_JOL_Statistics_StartTime VARCHAR(50),
      @param_JOL_Statistics_EndTime VARCHAR(50)
	)
AS
SET @param_JOL_Statistics_StartTime = @param_JOL_Statistics_StartTime + '-01'
SET @param_JOL_Statistics_EndTime = @param_JOL_Statistics_EndTime + '-01'
DECLARE @FacultyID VARCHAR(50), @Lecture int, @Recruitment int, @CareerTalk int, @TheOthers int
DECLARE @Count int, @NUM int , @i int

WHILE @param_JOL_Statistics_StartTime <= @param_JOL_Statistics_EndTime
BEGIN
	SELECT DISTINCT fdCollID INTO #1 FROM[dbo].[FS_College]
	ALTER TABLE #1 ADD [ID] INT IDENTITY
	SET @NUM = (SELECT COUNT(*) FROM #1)
	SET @i = 1
	
	WHILE @i <= @NUM
	BEGIN
		SET @FacultyID = (SELECT fdCollID FROM #1 WHERE ID = @i)
		SET @i = @i + 1
		
		SET @Lecture =(SELECT count(*)  
			FROM [dbo].[JOL_FacultyActivities]
			WHERE JOL_FacultyActivities_FacultyID = @FacultyID
				AND YEAR(JOL_FacultyActivities_StartTime) = YEAR(@param_JOL_Statistics_StartTime) 
				AND MONTH(JOL_FacultyActivities_StartTime) = MONTH(@param_JOL_Statistics_StartTime)
				AND JOL_FacultyActivities_Type = '宣讲会'
				AND JOL_FacultyActivities_VerifyState = '已通过')
		SET @Recruitment = (SELECT count(*) 
			FROM [dbo].[JOL_FacultyActivities]
			WHERE JOL_FacultyActivities_FacultyID = @FacultyID
				AND YEAR(JOL_FacultyActivities_StartTime) = YEAR(@param_JOL_Statistics_StartTime) 
				AND MONTH(JOL_FacultyActivities_StartTime) = MONTH(@param_JOL_Statistics_StartTime)
				AND JOL_FacultyActivities_Type = '招聘会'
				AND JOL_FacultyActivities_VerifyState = '已通过')
		SET @CareerTalk = (SELECT count(*) 
			FROM [dbo].[JOL_FacultyActivities]
			WHERE JOL_FacultyActivities_FacultyID = @FacultyID
				AND YEAR(JOL_FacultyActivities_StartTime) = YEAR(@param_JOL_Statistics_StartTime) 
				AND MONTH(JOL_FacultyActivities_StartTime) = MONTH(@param_JOL_Statistics_StartTime)
				AND JOL_FacultyActivities_Type = '就业讲座'
				AND JOL_FacultyActivities_VerifyState = '已通过')
		SET @TheOthers = (SELECT count(*) 
			FROM [dbo].[JOL_FacultyActivities]
			WHERE JOL_FacultyActivities_FacultyID = @FacultyID
				AND YEAR(JOL_FacultyActivities_StartTime) = YEAR(@param_JOL_Statistics_StartTime) 
				AND MONTH(JOL_FacultyActivities_StartTime) = MONTH(@param_JOL_Statistics_StartTime)
				AND JOL_FacultyActivities_Type = '其他'
				AND JOL_FacultyActivities_VerifyState = '已通过')

		SET @Count = (SELECT COUNT(*) FROM dbo.JOL_Statistics_FacultyActivity 
			WHERE Statistics_FacultyID = @FacultyID AND Statistics_Date = @param_JOL_Statistics_StartTime)
		IF (@Count = 0)
			BEGIN
				INSERT INTO [dbo].[JOL_Statistics_FacultyActivity]
				(
					Statistics_FacultyID,
					Statistics_Date,
					Statistics_Lecture,
					Statistics_Recruitment,
					Statistics_CareerTalk,
					Statistics_TheOthers,
					Statistics_AddTime,
					Statistics_UpdateTime
				)
				VALUES
				(
					@FacultyID,
					@param_JOL_Statistics_StartTime,
					@Lecture,
					@Recruitment,
					@CareerTalk,
					@TheOthers,
					GETDATE(),
					GETDATE()
				)
			END
		ELSE
			BEGIN
				UPDATE [dbo].[JOL_Statistics_FacultyActivity]
					SET Statistics_Lecture = @Lecture,
						Statistics_Recruitment = @Recruitment,
						Statistics_CareerTalk = @CareerTalk,
						Statistics_TheOthers = @TheOthers,
						Statistics_UpdateTime = GETDATE()
				WHERE Statistics_FacultyID = @FacultyID AND Statistics_Date = @param_JOL_Statistics_StartTime
			END
	END
	DROP TABLE #1
	SET @param_JOL_Statistics_StartTime = CONVERT(VARCHAR(50), DATEADD(MONTH, 1, @param_JOL_Statistics_StartTime), 23)
END

GO
